﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using DataAccess;
using System.Collections;
using System.Configuration;
using System.Data.SqlClient;

namespace Business {
    public class DataSheetService {
        CreateConnection cn = new CreateConnection();
        public void updateDataByID(DataTable components) {
            CreateConnection cn = new CreateConnection();

            string query = "";
            foreach (DataRow r in components.Rows) {
                string id = r["component_id"].ToString();
                string attribute = r["Attributes"].ToString();
                string value = r["Values"].ToString();
                query = query + "update ComponentTechnicalData set value='" +
                             value + "' where Component_id=" + id + " and attribute='" + attribute + "';";
            }
            query = query + " commit;";
            cn.RunCommand(query);

        }
        public DataTable getDataByComponentID(DataTable componentID) {
            CreateConnection cn = new CreateConnection();
            DataTable DataSheet = new DataTable();
            string componentList = "(";
            bool first = true;
            string componentId = "";

            foreach (DataRow r in componentID.Rows) {
                componentId = r["component_id"].ToString();
                if (first == true) {
                    componentList = componentList + componentId;
                    first = false;
                } else
                    componentList = componentList + "," + componentId;
            }
            componentList = componentList + ")";
            string query = "";
            query = "SELECT  Component_id, Attribute, Value " +
                    "FROM            ComponentTechnicalData " +
                    "WHERE        (Component_id IN " + componentList + ");";
            DataSheet = cn.CreateDataTable(query);
            return DataSheet;

        }

        public DataTable getDataByComponentID(string component_id) {
            CreateConnection cn = new CreateConnection();
            DataTable DataSheet = new DataTable();
            string query = "SELECT  Attribute, Value " +
                    "FROM            ComponentTechnicalData " +
                    "WHERE        (Component_id = " + component_id + ") order by componenttech_id;";
            DataSheet = cn.CreateDataTable(query);

            return DataSheet;
        }
        public string getComponentType(string componnent_id) {
            CreateConnection cn = new CreateConnection();
            string type = "0";
            DataTable t = new DataTable();
            string query = "select category_id from components where component_id=" + componnent_id + ";";
            t = cn.CreateDataTable(query);
            foreach (DataRow r in t.Rows) {
                type = r["category_id"].ToString();
            }
            return type;
        }
        public DataTable getDataByTypeID(string type) {
            CreateConnection cn = new CreateConnection();
            DataTable t = new DataTable();
            string query = " select attribute from categoryData where category_id=" + type + ";";
            t = cn.CreateDataTable(query);
            return t;
        }
        public DataTable getCategory() {
            CreateConnection cn = new CreateConnection();
            DataTable dt = new DataTable();
            string query = "select category_id, category_Name from Category;";
            dt = cn.CreateDataTable(query);
            return dt;
        }
        public DataTable getCategoryTemplateByID(int category_id) {
            CreateConnection cn = new CreateConnection();
            DataTable dt = new DataTable();
            string query = "select attribute from categoryData where category_id=" + category_id + ";";
            dt = cn.CreateDataTable(query);
            return dt;

        }
        public void updateComponentTechnicalData(DataTable dt, string component_id) {


            CreateConnection cn = new CreateConnection();
            string queryDelete = "DELETE componentTechnicalData where component_id=" + component_id + ";";
            string queryInsert = "";
            foreach (DataRow r in dt.Rows) {
                string attribute = r["Attribute"].ToString();
                string value = r["Value"].ToString();
                queryInsert = queryInsert + "insert into componentTechnicalData(attribute,value,component_id) values('" +
                                             attribute + "','" + value + "'," + component_id + ");";

            }
            cn.RunCommand(queryDelete);
            if (queryInsert != "")
                cn.RunCommand(queryInsert);

        }
        public void updateCategoryData(DataTable dt, string category_id) {

            CreateConnection cn = new CreateConnection();
            string queryDelete = "DELETE CategoryData where category_id=" + category_id + ";";
            string queryInsert = "";
            foreach (DataRow r in dt.Rows) {
                string attribute = r["Attribute"].ToString();

                queryInsert = queryInsert + "insert into CategoryData(attribute,category_id) values('" +
                                             attribute + "'," + category_id + ");";

            }
            cn.RunCommand(queryDelete);
            if (queryInsert != "")
                cn.RunCommand(queryInsert);

        }
    }
}
